<?php
/**
 * Description:合同导入相关
 * User: wuhao
 * Date: 2017-06-21
 * Time: 14:16
 */
namespace app\backend\controller;
use app\backend\controller\traits\AuthManager;
use app\backend\model\Building;
use app\backend\model\ExcelFile;
use app\backend\model\Lease;
use app\backend\model\Member;
use Business\Business;
use think\Controller;

class LeaseImportController extends Controller
{
    use AuthManager;
    private $_memberId = null;
    private $_dtype    = null;
    private $_pdtype   = null;
    private $_pmid     = null;
    private $_allAuth  = [];
    public function __construct()
    {
        parent::__construct();
        $memberInfo = $this->getLoginInfo();
        $this->_memberId = $memberInfo['mid'];
        $this->_dtype    = $memberInfo['dtype'];
        $this->_pdtype   = $memberInfo['p_dtype'];
        $this->_pmid     = $memberInfo['p_mid'];
        $this->_allAuth  = $this->getPower($this->_memberId, $this->_dtype);
    }


    /**
     * 获取租赁合约模型
     * @return Lease
     */
    public function getLeaseModel()
    {
        static $model;
        if (!$model) {
            $model = new Lease();
        }
        return $model;
    }

    /**
     * 获取租赁合约模型
     * @return Building
     */
    public function getBuildingModel()
    {
        static $model;
        if (!$model) {
            $model = new Building();
        }
        return $model;
    }

    /**
     * 获取用户模型
     * @return Member
     */
    public function getMemberModel()
    {
        static $model;
        if (!$model) {
            $model = new Member();
        }
        return $model;
    }

    /**
     * 导出用户模板
     * @date   2017-06-21
     * @author wuhao
     *
     */
    public function exportModel()
    {
        if (!in_array($this->_pdtype, [3, 4])) {
            $this->apiReturn(400, [], '无权操作');
        }
        $buildingId = I('building_id', '', 'intval');
        if (!$buildingId) {
            $this->apiReturn(400, [], '请选择楼宇');
        }
        $where = ['id' => $buildingId, 'mid' => $this->_pmid, 'is_delete' => 0];
        $buildingInfo = $this->getBuildingModel()->getBuildingList($where, '*');
        if (!$buildingInfo) {
            $this->apiReturn(400, [], '楼宇有误');
        }
        //获取未出租的对应的房源
        if ($this->_dtype != 7) {
            $houseList = $this->getBuildingModel()->getHouseList(['building_id'=>$buildingId, 'status'=>2, 'is_delete'=>0], 'building_id, number, room');
        } else {
            //员工
            $houseList = $this->getBuildingModel()->getHouseList(['id' => ['in', $this->houseAuth($this->_memberId)], 'building_id'=>$buildingId, 'status'=>2, 'is_delete'=>0], 'building_id, number, room');
        }

        if (!$houseList) {
            $this->apiReturn(400, [], '没有可出租的房源');
        }

        //导出的相关设置
        $fileName = '批量合同';
        $first = ['A1' => '房源信息', 'D1' => '租客合同信息'];
        $second = ['A2' => '房源信息', 'D2' => '租客信息', 'G2'=>'合同信息'];
        $title = ['A3' => '楼宇ID', 'B3' => '楼栋', 'C3' => '房号', 'D3' => '租客名称', 'E3' => '手机号', 'F3' => '财务联系电话',
         'G3' => '租赁开始时间（YYYYMMDD）', 'H3' => '租赁结束时间（YYYYMMDD）', 'I3' => '付款周期', 'J3' => '免租期（天）', 'K3' => '收款账单日（默认5）'];
        $width = ['A' => 15, 'B' => 15, 'C' => 15, 'D' => 15, 'E' => 20, 'F' => 20, 'G' => 28, 'H' => 28, 'I' => 20, 'J' => 20, 'K' => 20];
        $objPHPExcel     = load_excel();
        $objWriter       = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle($fileName);
        //都设置为文本格式
        $objPHPExcel->getActiveSheet()->getStyle('A:L')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);

        //第一行
        //合并单元格
        $objPHPExcel->getActiveSheet()->mergeCells('A1:C1');
        $objPHPExcel->getActiveSheet()->mergeCells('D1:K1');
        //填充颜色
        $objPHPExcel->getActiveSheet()->getStyle( 'A1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()->setRGB('#98F5FF');
        $objPHPExcel->getActiveSheet()->getStyle( 'D1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()->setRGB(\PHPExcel_Style_COLOR::COLOR_YELLOW);
        //位置格式
        $align = ['font' => ['bold' => false], 'alignment' => ['horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER]];
        //水平居中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($align);
        $objPHPExcel->getActiveSheet()->getStyle('D1')->applyFromArray($align);
        foreach ($first as $a => $b) {//设置列名
            $objPHPExcel->getActiveSheet()->setCellValue($a, $b);
        }

        //第二行
        $objPHPExcel->getActiveSheet()->mergeCells('A2:C2');
        $objPHPExcel->getActiveSheet()->mergeCells('D2:F2');
        $objPHPExcel->getActiveSheet()->mergeCells('G2:K2');
        foreach ($second as $a => $b) {//设置列名
            $objPHPExcel->getActiveSheet()->setCellValue($a, $b);
        }
        $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($align);
        $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray($align);
        $objPHPExcel->getActiveSheet()->getStyle('G2')->applyFromArray($align);

        //第三行
        foreach ($width as $a => $b) {//设置每列宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension($a)->setWidth($b);
        }
        foreach ($title as $c => $d) {//设置列名
            $objPHPExcel->getActiveSheet()->setCellValue($c, $d);
        }
        $objPHPExcel->getActiveSheet()->getStyle( 'A3:J3')->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_RED);

        //第四行
        $objPHPExcel->getActiveSheet()->mergeCells('A4:K4');
        //设置备注
        $comment = '备注（导入时请不要删除这栏）：1. 红色字段为必填字段；2. 用户信息不存在会自动创建；3.付款周期 1=月， 2=季度 ,3=半年，4=年，请输入对应的数字';
        $objPHPExcel->getActiveSheet()->setCellValue('A4', $comment);

        //房源数据
        $num = 5;
        foreach ($houseList as $h) {
            $objPHPExcel->getActiveSheet()->setCellValue('A'.$num, $h['building_id']);
            $objPHPExcel->getActiveSheet()->setCellValue('B'.$num, $h['number']);
            $objPHPExcel->getActiveSheet()->setCellValue('C'.$num, $h['room']);
            ++$num;
        }

        $saveName = $fileName . date('Ymd') . '.xls';
        ob_end_clean();
        header('Pragma: public');
        header('Expires: 0');
        header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
        header('Content-Type:application/force-download');
        header('Content-Type:application/vnd.ms-execl');
        header('Content-Type:application/octet-stream');
        header('Content-Type:application/download');
        header("Content-Disposition:attachment;filename={$saveName}");
        header('Content-Transfer-Encoding:binary');
        $objWriter->save('php://output');
    }

    /**
     * 读取并验证excel数据
     * @date   2017-06-27
     * @author wuhao
     *
     * @param string  $file  文件地址
     * @param string  $filename 文件名
     */
    public function readExcel()
    {
        if (!in_array($this->_pdtype, [3, 4])) {
            $this->apiReturn(400, [], '无权操作');
        }
        $file = I('file', '', 'strval');
        $filename = I('filename', '', 'strval');
        //$file = "http://zft.12301.local/excel/property_manager/access/60/14967147734451.xls";
        //$filename = '物业水电管理.xls';
        if (!$file) {
            $this->apiReturn(400, [], '文件地址有误');
        }

        //获取这个文件的地址,将主域名替换成目录名
        $fileInfo = str_replace(MAIN_DOMAIN, EXCEL_REMOVE_DIR, $file);
        $excel = pathinfo($fileInfo);
        //dd($excel);
        // 文件类型校验
        if (!in_array($excel['extension'], ['xls', 'xlsx', 'csv'])) {
            $this->apiReturn(400, [], '不是excel文件');
        }

        $e = file_get_contents($fileInfo);
        if (!$e) {
            $this->apiReturn(400, [], '文件获取失败');
        }

        //先获取excel数据
        $res = $this->_getData($fileInfo);
        if (!$res) {
            unlink($fileInfo);//删除这个文件
            $this->apiReturn(400, [], '所传的文件数据为空，请重新上传');
        }

        $countTotal = count($res);
        $fileModel  = new ExcelFile();
        $fileId     = $fileModel->addFile($filename, $this->_pmid, $file, date('Y-m-d H:i:s'), $countTotal, 4, 4);
        if (!$fileId) {
            zft_log('property_manager', $fileModel->getLastSql());
            $this->apiReturn(400, [], '记录文件失败');
        }

        //验证数据3秒后后台跑
        sleep(3);
        set_time_limit(0);
        $resData     = $this->_checkData($res);
        $accessData  = $resData['access'];
        $wrongData   = $resData['wrong'];
        $countAccess = count($accessData);
        $countWrong  = count($wrongData);
        $total       = $countWrong + $countAccess;

        if (!$wrongData) {
            $business = new Business();
            $tmpNum = 1000;
            $time = strval(time());
            $preTime = substr($time, 0, 8);//截取前8位
            foreach ($accessData as $v) {
                //先判断租户是不是已存在
                $tmpId = $this->getMemberModel()->getMemberInfoByMobile($v[4], 'id');
                if ($tmpId) {
                    $tenantId = $tmpId['id'];
                } else {
                    //不存在新建用户信息
                    $account = $business->_generateAccount(2);
                    $member   = [
                        'dtype'       => 2,
                        'username'    => $account,
                        'mobile'      => $v[4],
                        'password'    => md5(md5('123456')),
                        'withdraw_pwd'=> '',
                        'status'      => 1,
                        'nickname'    => $v[3],
                        'id_card'     => '',
                        'license'     => '',
                        'user_type'   => 0,
                        'address'     => '',
                        'update_time' => time(),
                        'create_time' => time(),
                    ];
                    $tenantId = $this->getMemberModel()->addMember($member);
                    if (!$tenantId) {
                        zft_log('member_add', $this->getMemberModel()->getLastSql());
                        continue;
                    } else {//新增成功
                        //添加余额信息
                        $tmp = [
                            'mid'   => $tenantId,
                            'money' => 0,
                        ];
                        $resAddMoney = $this->getMemberModel()->addMemberMoney($tmp);
                        if (!$resAddMoney) {
                            zft_log('add_money_fail', json_encode($tmp));
                        }

                        //新增关联关系
                        $value = ['parent_id' => $this->_pmid, 'son_id' => $tenantId];
                        $res = $this->getMemberModel()->addRelation($value);
                        if (!$res) {
                            zft_log('add_relation_fail', json_encode($this->getMemberModel()->getLastSql()));
                        }
                    }
                    unset($member);
                }
                //获取业主的id
                $houseInfo =  $this->getBuildingModel()->getHouseInf($v[0], $v[1], $v[2], 'id, house_number, mid');

                //插入合约
                $data = [
                    'tenant_id'      => $tenantId,      //租户id
                    'owner_id'       => $houseInfo['mid'],       //业主id
                    'create_user'    => $this->_pmid,//帮忙签约的人的id
                    'building_id'    => $v[0],    //楼宇id
                    'ordernum'       => $preTime . $tmpNum,     //合同编号
                    'status'         => 2,
                    'industry'       => 0,      //行业属性
                    'ordermode'      => 0,    //合约类型0个人 1企业
                    'ordertime'      => time(),
                    'begin_time'     => $v[6],          //合同开始时间时间
                    'free_day'       => $v[9],          //免租天数
                    'end_time'       => $v[7],          //结束租赁时间
                    'charge_type'    => $v[8],         //收费周期类型
                    'lease_mode'     => 2,             //租赁方式 1托管 2租赁
                    'billing_day'    => $v[10] ? $v[10] : 5,  //账单日 (按每周期)
                    'id_card_a'      => '',              //身份证正面
                    'id_card_b'      => '',             //身份证反面
                    'tax'            => 0,              //税率
                    'legal_id_card'  => '',             //法人身份证信息
                    'finance_mobile' => $v[5],          //财务联系电话
                    'finance_user'   => '',             //财务联系人姓名
                    'growth_rate'    => json_encode([]),//增长率
                    'discount'       => 1000,      //第一年折扣率
                    'contract_time'  => 0,          //签约时间
                ];
                //插入数据
                $leaseId = $this->getLeaseModel()->insertContractInfo($data);//插入合约基本信息
                if (!$leaseId) {
                    zft_log('lease/add_lease', $this->getLeaseModel()->getLastSql());
                    continue;
                } else {
                    //再插入ext表
                    $houseNumber = $houseInfo['house_number'];
                    $houseId     = $houseInfo['id'];
                    $extData = [];
                    $extData[] = [
                        'ordernum'     => $preTime . $tmpNum,
                        'house_id'     => $houseId,
                        'house_number' => $houseNumber,
                        'number'       => $v[1],
                        'room'         => $v[2],
                        'water'        => 0,
                        'electric'     => 0,
                        'create_time'  => time(),
                        'update_time'  => time(),
                        'status'       => 2,
                    ];
                    $extRes  = $this->getLeaseModel()->insertExtInfo($extData);
                    if (!$extRes) {
                        zft_log('lease/add_lease_ext', $this->getLeaseModel()->getLastSql());
                    }
                }
                $tmpNum++;

                //更新对应房源状态
                $update = $this->getBuildingModel()->updateHouse(['status' => 1],
                    ['building_id' => $v[0], 'number' => $v[1], 'room' => $v[2]]);
                if (!$update) {
                    zft_log('build/update_house', $this->getBuildingModel()->getLastSql());
                }
                unset($extData, $extRes, $leaseId, $houseNumber, $houseInfo, $tenantId, $tmpId, $data, $houseId);
            }

            //没有错误的数据
            $updateData = [
                'record'          => $total,
                'access_record'   => $countAccess,
                'validate_status' => 1,
                'status'          => 1,
            ];
            $res = $fileModel->updateFile($fileId, $updateData);
            if (!$res) {
                zft_log('member_import', $fileModel->getLastSql());
            }
            echo 1;
            exit();
        } else {
            //保存错误数据到模板
            $wrongUrl = $this->_saveWrongModel($accessData, $wrongData);
            $updateData = [
                'record'          => $total,
                'access_record'   => $countAccess,
                'validate_status' => 1,
                'status'          => 0,
                'wrong_url'       => $wrongUrl
            ];
            $res = $fileModel->updateFile($fileId, $updateData);
            if (!$res) {
                zft_log('member_import', $fileModel->getLastSql());
            }
            echo 2;
            exit();
        }
    }

    /**
     * 读取excel数据
     * @date   2017-06-27
     * @author wuhao
     *
     * @param string  $fileInfo  文件的绝对地址
     * @return array
     */
    private function _getData($fileInfo)
    {
        $objPHPExcel = load_excel();
        $reader = \PHPExcel_IOFactory::createReader('Excel5');
        $phpExcel = $reader->load($fileInfo,$encode='utf-8');

        //读取excel数据
        $sheet = $phpExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $highestColumm = $sheet->getHighestColumn(); // 取得总列数

        $tmpData = [];
        for ($row = 5; $row <= $highestRow; $row++) {//内容是以第5行开始
            for ($column = 'A'; $column <= $highestColumm; $column++) {//列数是以A列开始
                $tmp = $sheet->getCell($column.$row)->getValue(); //读取到数组中
                if (is_object($tmp)) {
                    $tmp = $tmp->__toString();
                }

                $tmpData[$row][] = trim($tmp); //过滤空串，读取到数组中
                unset($tmp);
            }
        }
        $tmpData = array_merge($tmpData);
        $data = $tmpData;
        unset($dataset, $highestRow, $highestColumm);

        $res = [];
        foreach ($data as $k => $v) {
            if ((!$v[3] && !$v[4] && !$v[5]) || (!$v[6] && !$v[7] && !$v[8] && !$v[9])) {
                //租户信息或合同信息都为空的过滤
                continue;
            } else {
                $res[] = $v;
            }
        }
        return $res;
    }

    /**
     * 验证数据,返回正确的和错误的
     * @date   2017-06-26
     * @author wuhao
     *
     * @param array  $data  要验证的数据
     * @return array ['access' => array, 'wrong' => array]
     */
    private function _checkData($data)
    {
        $accessData = $wrongData = [];
        $wrongComm  = [
            0  => '楼宇id有误',
            1  => '楼层有误',
            2  => '房号不能为空',
            3  => '租客名称不能为空',
            4  => '租客手机号有误',
            5  => '财务联系电话有误',
            6  => '租赁开始周期有误',
            7  => '租赁结束周期有误',
            8  => '付款周期有误',
            9  => '免租期有误',
            10 => '收费账单日有误',
            11 => '楼宇id不存在',
            12 => '该房源不存在',
            13 => '该房源已出租',
            14 => '所传手机号对应的用户不是租户',
        ];

        //再判断格式
        foreach ($data as $k => $v) {
            //基本的判断
            if (!$v[0] || !is_numeric($v[0]) || $v[0] <=0) {//楼宇id
                $data[$k]['comment'] = $wrongComm[0];
                $wrongData[]  = $data[$k];
                continue;
            }
            if (!$v[1] ) {//楼层
                $data[$k]['comment'] = $wrongComm[1];
                $wrongData[]  = $data[$k];
                continue;
            }
            if (!$v[2]) {//房号
                $data[$k]['comment'] = $wrongComm[2];
                $wrongData[]  = $data[$k];
                continue;
            }
            if (!$v[3]) {//租客名称
                $data[$k]['comment'] = $wrongComm[3];
                $wrongData[]  = $data[$k];
                continue;
            }
            if (!$v[4] || !ismobile($v[4])) {//手机号
                $data[$k]['comment'] = $wrongComm[4];
                $wrongData[]  = $data[$k];
                continue;
            }
            if (!$v[5] || !ismobile($v[5])) {//财务联系电话
                $data[$k]['comment'] = $wrongComm[5];
                $wrongData[]  = $data[$k];
                continue;
            }
            if (!$v[6] || !is_numeric($v[6]) || !strtotime($v[6])) {//开始周期
                $data[$k]['comment'] = $wrongComm[6];
                $wrongData[]  = $data[$k];
                continue;
            }
            if (!$v[7] || !is_numeric($v[7]) || !strtotime($v[7])) {//结束周期
                $data[$k]['comment'] = $wrongComm[7];
                $wrongData[]  = $data[$k];
                continue;
            }
            if (!$v[8] || !in_array($v[8], [1,2,3,4])) {//账单周期
                $data[$k]['comment'] = $wrongComm[8];
                $wrongData[]  = $data[$k];
                continue;
            }
            if (!is_numeric($v[9]) || $v[9] <0) {//免租期
                $data[$k]['comment'] = $wrongComm[9];
                $wrongData[]  = $data[$k];
                continue;
            }
            if ($v[10]) {//账单日
                if (!is_numeric($v[10]) || $v[10] <=0 || $v[10] >= 29) {
                    $data[$k]['comment'] = $wrongComm[10];
                    $wrongData[]  = $data[$k];
                    continue;
                }
            } else {//为空设为5
                $data[$k][10] = 5;
            }

            //其他的判断
            if (!$this->getBuildingModel()->getBuildingList(['id' => $v[0], 'mid' => $this->_pmid, 'is_delete'=>0], 'id')) {//判断楼宇是否存在
                $data[$k]['comment'] = $wrongComm[11];
                $wrongData[]  = $data[$k];
                continue;
            }

            $status = $this->getBuildingModel()->getHouseInf($v[0], $v[1], $v[2], 'status');//判断房源状态
            if (!$status) {
                $data[$k]['comment'] = $wrongComm[12];
                $wrongData[]  = $data[$k];
                continue;
            } else {
                if ($status['status'] == 1) {
                    $data[$k]['comment'] = $wrongComm[13];
                    $wrongData[]  = $data[$k];
                    continue;
                }
            }

            //判断用户
            $type = $this->getMemberModel()->getMemberInfoByMobile($v[4], 'dtype');
            if ($type && $type['dtype'] != 2) {
                $data[$k]['comment'] = $wrongComm[14];
                $wrongData[]  = $data[$k];
                continue;
            }
            unset($status);
            $accessData[] = $data[$k];
        }

        $res = [
            'access' => $accessData,
            'wrong'  => $wrongData,
        ];
        return $res;
    }

    /**
     * 导出存在错误的数据模板
     * @date   2017-06-26
     * @author wuhao
     *
     * @param array       $accessData   正确的数据
     * @param array       $wrongData    错误的数据
     * @return string 文件地址
     */
    private function _saveWrongModel($accessData, $wrongData)
    {
        //设置导出样式等
        $fileName = '批量合同';
        $first = ['A1' => '房源信息', 'D1' => '租客合同信息'];
        $second = ['A2' => '房源信息', 'D2' => '租客信息', 'G2'=>'合同信息'];
        $title = ['A3' => '楼宇ID', 'B3' => '楼栋', 'C3' => '房号', 'D3' => '租客名称', 'E3' => '手机号', 'F3' => '财务联系电话',
                  'G3' => '租赁开始时间（YYYYMMDD）', 'H3' => '租赁结束时间（YYYYMMDD）', 'I3' => '付款周期', 'J3' => '免租期（天）',
                  'K3' => '收款账单日（默认5）', 'L3' => '错误提示'];
        $width = ['A' => 15, 'B' => 15, 'C' => 15, 'D' => 15, 'E' => 20, 'F' => 20, 'G' => 28, 'H' => 28, 'I' => 20, 'J' => 20, 'K' => 20, 'L' => 50];
        $objPHPExcel     = load_excel();
        $objWriter       = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle($fileName);
        //都设置为文本格式
        $objPHPExcel->getActiveSheet()->getStyle('A:L')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);

        //第一行
        //合并单元格
        $objPHPExcel->getActiveSheet()->mergeCells('A1:C1');
        $objPHPExcel->getActiveSheet()->mergeCells('D1:K1');
        //填充颜色
        $objPHPExcel->getActiveSheet()->getStyle( 'A1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()->setRGB('#98F5FF');
        $objPHPExcel->getActiveSheet()->getStyle( 'D1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()->setRGB(\PHPExcel_Style_COLOR::COLOR_YELLOW);
        //位置格式
        $align = ['font' => ['bold' => false], 'alignment' => ['horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER]];
        //水平居中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($align);
        $objPHPExcel->getActiveSheet()->getStyle('D1')->applyFromArray($align);
        foreach ($first as $a => $b) {//设置列名
            $objPHPExcel->getActiveSheet()->setCellValue($a, $b);
        }

        //第二行
        $objPHPExcel->getActiveSheet()->mergeCells('A2:C2');
        $objPHPExcel->getActiveSheet()->mergeCells('D2:F2');
        $objPHPExcel->getActiveSheet()->mergeCells('G2:K2');
        foreach ($second as $a => $b) {//设置列名
            $objPHPExcel->getActiveSheet()->setCellValue($a, $b);
        }
        $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($align);
        $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray($align);
        $objPHPExcel->getActiveSheet()->getStyle('G2')->applyFromArray($align);

        //第三行
        foreach ($width as $a => $b) {//设置每列宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension($a)->setWidth($b);
        }
        foreach ($title as $c => $d) {//设置列名
            $objPHPExcel->getActiveSheet()->setCellValue($c, $d);
        }
        $objPHPExcel->getActiveSheet()->getStyle( 'A3:J3')->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_RED);

        //第四行
        $objPHPExcel->getActiveSheet()->mergeCells('A4:K4');
        //设置备注
        $comment = '备注（导入时请不要删除这栏）：1. 红色字段为必填字段；2. 用户信息不存在会自动创建；3.付款周期 1=月， 2=季度 ,3=半年，4=年，请输入对应的数字';
        $objPHPExcel->getActiveSheet()->setCellValue('A4', $comment);

        //导入具体数据
        $num=4;
        if ($accessData) {
            foreach ($accessData as $a => $b) {
                if ($b) {
                    ++$num;
                    $objPHPExcel->getActiveSheet()->setCellValue('A'.$num, $b[0]);
                    $objPHPExcel->getActiveSheet()->setCellValue('B'.$num, $b[1]);
                    $objPHPExcel->getActiveSheet()->setCellValue('C'.$num, $b[2]);
                    $objPHPExcel->getActiveSheet()->setCellValue('D'.$num, $b[3]);
                    $objPHPExcel->getActiveSheet()->setCellValue('E'.$num, $b[4]);
                    $objPHPExcel->getActiveSheet()->setCellValue('F'.$num, $b[5]);
                    $objPHPExcel->getActiveSheet()->setCellValue('G'.$num, $b[6]);
                    $objPHPExcel->getActiveSheet()->setCellValue('H'.$num, $b[7]);
                    $objPHPExcel->getActiveSheet()->setCellValue('I'.$num, $b[8]);
                    $objPHPExcel->getActiveSheet()->setCellValue('J'.$num, $b[9]);
                    $objPHPExcel->getActiveSheet()->setCellValue('K'.$num, $b[10]);
                }
            }
        }

        if ($wrongData) {
            foreach ($wrongData as $c => $d) {
                if ($d) {
                    ++$num;
                    //错误的记录设置背景色
                    $objPHPExcel->getActiveSheet()->getStyle('A'.$num.':'.'L'.$num)->getFill()
                        ->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)
                        ->getStartColor()->setRGB('FFFF00');

                    $objPHPExcel->getActiveSheet()->setCellValue('A'.$num, $d[0]);
                    $objPHPExcel->getActiveSheet()->setCellValue('B'.$num, $d[1]);
                    $objPHPExcel->getActiveSheet()->setCellValue('C'.$num, $d[2]);
                    $objPHPExcel->getActiveSheet()->setCellValue('D'.$num, $d[3]);
                    $objPHPExcel->getActiveSheet()->setCellValue('E'.$num, $d[4]);
                    $objPHPExcel->getActiveSheet()->setCellValue('F'.$num, $d[5]);
                    $objPHPExcel->getActiveSheet()->setCellValue('G'.$num, $d[6]);
                    $objPHPExcel->getActiveSheet()->setCellValue('H'.$num, $d[7]);
                    $objPHPExcel->getActiveSheet()->setCellValue('I'.$num, $d[8]);
                    $objPHPExcel->getActiveSheet()->setCellValue('J'.$num, $d[9]);
                    $objPHPExcel->getActiveSheet()->setCellValue('K'.$num, $d[10]);
                    $objPHPExcel->getActiveSheet()->setCellValue('L'.$num, $d['comment']);
                }
            }
        }


        $fileName = $fileName.date('Ymd_His').'.xls';
        $year = date('Y');
        $month = date('m');
        $day = date('d');
        $time = $year.'/'.$month.'/'.$day;
        $pathStr = EXCEL_IMPORT_DIR.'lease/wrong/' . $time;
        $downloadStr = MAIN_DOMAIN.'excel/lease/wrong/' . $time;
        createDir($pathStr);
        $objWriter->save($pathStr.'/'.$fileName);
        return $downloadStr.'/'.$fileName;
    }
}